﻿using System;
using System.Linq;
using System.Linq.Expressions;

namespace FlashPay.DAO.Impl
{
    using FlashPay.DAO.Interface;
    using FlashPay.EF;
    using FlashPay.EF.Models;
    using FlashPay.Entity;
    using FlashPay.Entity.Enum;
    using FlashPay.Entity.Parameter;
    using FlashPay.Entity.Request.Payment;
    using FlashPay.Entity.Response.Payment;
    using FlashPay.Util;
    using Microsoft.EntityFrameworkCore;
    using MySql.Data.MySqlClient;
    using System.Collections.Generic;
    using System.Data;

    /// <summary>
    /// 付款记录数据接口实现
    /// </summary>
    /// <remarks>2018-07-06 immi 创建</remarks>
    public class PaymentRecordDaoImpl : IDisposable,PaymentRecordDao
    {
        #region 注入
        /// <summary>
        /// EF上下文
        /// </summary>
        private FlashPayContext _context { set; get; }

        /// <summary>
        /// 日志模型
        /// </summary>
        private readonly LogRecord _logRecord;

        /// <summary>
        /// 注入
        /// </summary>
        /// <param name="context"></param>
        public PaymentRecordDaoImpl(FlashPayContext context, LogRecord logRecord)
        {
            _context = context;
            _logRecord = logRecord;
        }

        public void Dispose()
        {
            if (_context != null)
            {
                _context.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取订单月份
        /// </summary>
        /// <param name="month"></param>
        /// <returns></returns>
        private string GetOrderMonth(long orderNo)
        {
            return orderNo.ToString().Substring(4, 2);
        }

        /// <summary>
        /// 根据编号获取余额变化记录
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <returns>Company</returns>
        public PaymentRecord Get(long orderNo)
        {
            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"PaymentRecord_{month}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE OrderNo={orderNo}";

            var paymentRecord = _context.PaymentRecord
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return paymentRecord;
        }

        /// <summary>
        /// 根据编号获取余额变化记录
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <returns>Company</returns>
        private RecordReal GetRecordReal(long orderNo)
        {
            var tableName = $"RecordReal_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"RecordReal_{month}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE OrderNo={orderNo}";

            var recordReal = _context.RecordReal
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return recordReal;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="userInfo">新增对象</param>
        /// <returns></returns>
        public long Add(PaymentRecord model, FlashPayContext flashPayContext = null)
        {
            var insertSatatus = 0;
            var lastInsertId = 0L;

            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var connection = _context.Database.GetDbConnection();
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_InsertPaymentRecordWeb(@CompanyId, @PaymentRemark, @CreateDate,@ConfirmStatus, @ConfirmDate,@PaymentType,@WithdrawalOrderNo,@WithdrawalAmount,@WithdrawalBankName,@WithdrawalBankAddress,@WithdrawalAccountName, @WithdrawalCardNumber,@WithdrawalCardBankFlag,@DepositType);";
                command.Parameters.Add(new MySqlParameter("CompanyId", model.CompanyId));
                command.Parameters.Add(new MySqlParameter("PaymentRemark", model.PaymentRemark));
                command.Parameters.Add(new MySqlParameter("CreateDate", model.CreateDate.ToString("yyyy-MM-dd HH:mm:ss")));
                command.Parameters.Add(new MySqlParameter("ConfirmStatus", model.ConfirmStatus));
                command.Parameters.Add(new MySqlParameter("ConfirmDate", model.ConfirmDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
                command.Parameters.Add(new MySqlParameter("PaymentType", model.PaymentType));
                command.Parameters.Add(new MySqlParameter("WithdrawalOrderNo", model.WithdrawalOrderNo));
                command.Parameters.Add(new MySqlParameter("WithdrawalAmount", model.WithdrawalAmount));
                command.Parameters.Add(new MySqlParameter("WithdrawalBankName", model.WithdrawalBankName));
                command.Parameters.Add(new MySqlParameter("WithdrawalBankAddress", model.WithdrawalBankAddress));
                command.Parameters.Add(new MySqlParameter("WithdrawalAccountName", model.WithdrawalAccountName));
                command.Parameters.Add(new MySqlParameter("WithdrawalCardNumber", model.WithdrawalCardNumber));
                command.Parameters.Add(new MySqlParameter("WithdrawalCardBankFlag", model.WithdrawalCardBankFlag));
                command.Parameters.Add(new MySqlParameter("DepositType", model.DepositType));
                
                using (var insertResult = command.ExecuteReader())
                {
                    if (insertResult.Read())
                    {
                        insertSatatus = insertResult.GetInt32(0);
                        lastInsertId = insertResult.GetInt64(1);
                    }
                }

                if (insertSatatus <= 0)
                {
                    throw new Exception("更新付款RecordRealOrderNo失败！");
                }
            }

            return lastInsertId;

        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="CardMerchant">修改对象</param>
        /// <returns></returns>
        public bool Update(PaymentRecord model, FlashPayContext flashPayContext = null)
        {
            bool result = false;

            var adjustBalance = _context.PaymentRecord.Find(model.OrderNo);
            if (adjustBalance != null)
            {
                _context.SaveChanges();
                result = true;
            }
            return result;
        }

        /// <summary>
        /// UpdateRecordRealOrderNo
        /// </summary>
        /// <param name="CardMerchant">修改对象</param>
        /// <returns></returns>
        public bool UpdateRecordRealOrderNo(long orderNo,long recordRealOrderNo, FlashPayContext flashPayContext = null)
        {
            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"PaymentRecord_{month}";
            }

            var paymentRecord = this.Get(orderNo);
            if (paymentRecord != null)
            {
                var sql = $"update {tableName} set RecordRealOrderNo={recordRealOrderNo} where  OrderNo={orderNo}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }
                return true;
            }
            else
            {
                throw new Exception("当前记录不存在！");
            }
        }

        /// <summary>
        /// 付款取消
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public bool UpdatePaymentStatus(long orderNo, SByte paymentStatus)
        {
            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"PaymentRecord_{month}";
            }

            var paymentRecord = this.Get(orderNo);
            if (paymentRecord != null)
            {
                var sql = $"update {tableName} set PaymentStatus={paymentStatus} where  OrderNo={orderNo}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }
                return true;
            }
            else
            {
                throw new Exception("当前记录不存在！");
            }
        }

        /// <summary>
        /// 更新通知状态
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <param name="status">状态</param>
        /// <param name="noticeTimes">通知次数</param>
        /// <returns></returns>
        public bool UpdateNoticeStatus(long orderNo, SByte noticeStatus,int noticeTimes)
        {
            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);

            if (month != DateTime.Now.ToString("MM")) {
                tableName = $"PaymentRecord_{month}";
            }

            var paymentRecord = this.Get(orderNo);
            if (paymentRecord != null)
            {
                var sql = $"update {tableName} set NoticeStatus={noticeStatus},NoticeTimes='{noticeTimes}' where  OrderNo={orderNo}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("数据库操作失败！");
                }
                return true;
            }
            else
            {
                throw new Exception("当前记录不存在！");
            }
        }

        /// <summary>
        /// 付款确认
        /// </summary>
        /// <param name="request">参数</param>
        public bool PaymentConfirm(PaymentConfirmRequest request)
        {
            var connection = _context.Database.GetDbConnection();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_UpdatePaymentRecordByPaymentConfirm(@OrderNo, @ConfirmUID, @ConfirmName, @ConfirmStatus, @ConfirmDate);";
                command.Parameters.Add(new MySqlParameter("OrderNo", request.OrderNo));
                command.Parameters.Add(new MySqlParameter("ConfirmUID", request.ConfirmUID));
                command.Parameters.Add(new MySqlParameter("ConfirmName", request.ConfirmName));
                command.Parameters.Add(new MySqlParameter("ConfirmStatus", request.ConfirmStatus));
                command.Parameters.Add(new MySqlParameter("ConfirmDate", request.ConfirmDate.ToString("yyyy-MM-dd HH:mm:ss")));

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                var updateStatus = 0;

                using (var updateResult = command.ExecuteReader())
                {
                    if (updateResult.Read())
                    {
                        updateStatus = updateResult.GetInt32(0);
                    }
                }

                if (updateStatus <= 0)
                {
                    throw new Exception("更新付款RecordRealOrderNo失败！");
                }

                return true;
            }
        }

        /// <summary>
        /// 重新付款
        /// </summary>
        /// <param name="request"></param>
        public bool RenewPayment(RenewPaymentRequest request)
        {
            var connection = _context.Database.GetDbConnection();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_ResetFailedPaymentRecord(@OrderNo);";
                command.Parameters.Add(new MySqlParameter("OrderNo", request.OrderNo));

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                var updateStatus = 0;

                using (var updateResult = command.ExecuteReader())
                {
                    if (updateResult.Read())
                    {
                        updateStatus = updateResult.GetInt32(0);
                    }
                }

                if (updateStatus <= 0)
                {
                    throw new Exception("更新付款记录失败！");
                }

                return true;
            }
        }

        /// <summary>
        /// TLY重新付款
        /// </summary>
        /// <param name="request"></param>
        public bool TLYRenewPayment(RenewPaymentRequest request)
        {
            var connection = _context.Database.GetDbConnection();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_ResetTlyPaymentStatus(@OrderNo);";
                command.Parameters.Add(new MySqlParameter("OrderNo", request.OrderNo));

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                var updateStatus = 0;

                using (var updateResult = command.ExecuteReader())
                {
                    if (updateResult.Read())
                    {
                        updateStatus = updateResult.GetInt32(0);
                    }
                }

                if (updateStatus <= 0)
                {
                    throw new Exception("操作失败：该付款单不满足重新付款");
                }

                return true;
            }
        }

        /// <summary>
        ///【优凯顺】重新付款
        /// </summary>
        /// <param name="request"></param>
        public bool YksRenewPayment(RenewPaymentRequest request)
        {
            var connection = _context.Database.GetDbConnection();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_ResetUksPaymentStatus(@OrderNo);";
                command.Parameters.Add(new MySqlParameter("OrderNo", request.OrderNo));

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                var updateStatus = 0;

                using (var updateResult = command.ExecuteReader())
                {
                    if (updateResult.Read())
                    {
                        updateStatus = updateResult.GetInt32(0);
                    }
                }

                if (updateStatus <= 0)
                {
                    throw new Exception("操作失败：该付款单不满足重新付款");
                }

                return true;
            }
        }

        /// <summary>
        /// 检查订单是否
        /// </summary>
        /// <param name="query"></param>
        public List<PaymentRecord> CheckOrder(PaymentRecordQuery query)
        {
            var dt = DateTime.Now;

            var month = dt.AddDays(1 - dt.Day).AddMonths(-1).ToString("MM");

            var sql = $"SELECT * FROM PaymentRecord_{month} WHERE WithdrawalOrderNo='{query.WithdrawalOrderNo}'";
            var lastPaymentRecords = _context.PaymentRecord
                .FromSql(sql)
                .AsNoTracking()
                .ToList();
            if (lastPaymentRecords != null && lastPaymentRecords.Any()) {
                return lastPaymentRecords;
            }

            sql = $"SELECT * FROM PaymentRecord_{DateTime.Now.ToString("MM")} WHERE WithdrawalOrderNo='{query.WithdrawalOrderNo}'";

            var paymentRecords = _context.PaymentRecord
                .FromSql(sql)
                .AsNoTracking()
                .ToList();

            return paymentRecords;
        }

        /// <summary>
        /// 获取所有记录
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>List<UserInfo></returns>
        public List<PaymentRecord> GetList(PaymentRecordQuery query)
        {
            //多条件查询
            var where = PredicateBuilder.True<PaymentRecord>();

            //公司编号
            if (query.CompanyId.HasValue)
            {
                where = where.And(c => c.CompanyId == query.CompanyId.Value);
            }
            if (!string.IsNullOrEmpty(query.WithdrawalOrderNo))
            {
                where = where.And(c => c.WithdrawalOrderNo == query.WithdrawalOrderNo);
            }
            var list = _context.PaymentRecord.Where(where.Compile()).ToList();


            return list;
        }

        /// <summary>
        /// 付款卡查询
        /// </summary>
        /// <param name="query">查询参数</param>
        public DataGrid<PaymentCardResponse> GetPaymentCardPager(PaymentCardQuery query) {

            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";
            //配置
            var sysConfigs = _context.SysConfig.Where(c => c.ConfigCode == "PaymentInterface").ToList();

            var sql = $"select * from {tableName} where PaymentStatus={(sbyte)PaymentRecordPaymentStatus.付款成功} and PaymentDate >= '{DateTime.Now.ToString("yyyy-MM-dd 00:00:00")}' AND PaymentDate<='{DateTime.Now.ToString("yyyy-MM-dd 23:59:59")}'";
            var paymentRecords = _context.PaymentRecord
                .FromSql(sql)
                .AsNoTracking()
                .ToList();


            var q = from bc in _context.BankCard
                    let quantity = (from pr in paymentRecords where pr.PaymentCardId == bc.Bcid select pr).Count()
                    let withdrawalAmountSum = (from pr in paymentRecords where pr.PaymentCardId == bc.Bcid select pr).Sum(p=>p.WithdrawalAmount)
                    join
                    c in _context.Company on bc.CompanyId equals c.CompanyId
                    join
                    bcel in _context.BankCardExtraLimit on bc.CardNumber equals bcel.CardNumber 
                    where bc.CardType == (sbyte)CardType.付款卡.GetHashCode() && bc.CompanyId>0
                    select new
                    {
                        bc.Bcid,
                        c.CompanyId,
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        bc.CardType,
                        bc.UsingStatus,
                        bc.EnableStatus,
                        bc.Balance,
                        bc.PaymentStart,
                        bc.PaymentEnd,
                        //PaymentInterval
                        bc.PayFeeRatio,
                        bc.BalanceLastUpdate,
                        //TimeDifference
                        bc.PaymentType,
                        bc.DepositType,
                        bc.CrossBankPay,
                        bc.DepositFeeRatio,
                        bc.IpAddress,
                        bc.Remark,
                        bc.UsingStatusRemark,
                        bc.VmClientId,
                        Quantity = quantity,
                        WithdrawalAmount = withdrawalAmountSum,
                        bc.AdjustBalanceRid,
                        bcel.LimitPayAmount,
                        bcel.LimitPayCount
                    };
            //公司编号
            if (query.CompanyId.HasValue)
            {
                q = q.Where(c => c.CompanyId == query.CompanyId.Value);
            }
            //银行卡启用状态
            if (query.CompanyIds != null && query.CompanyIds.Any())
            {
                q = q.Where(c => query.CompanyIds.Contains(c.CompanyId));
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //银行卡
            if (!string.IsNullOrEmpty(query.BankName))
            {
                q = q.Where(c => c.BankName.Contains(query.BankName));
            }
            //银行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            //銀行卡号
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //银行卡使用状态
            if (query.UsingStatus.HasValue)
            {
                q = q.Where(c => c.UsingStatus == query.UsingStatus.Value);
            }
            //银行卡启用状态
            if (query.EnableStatus.HasValue)
            {
                q = q.Where(c => c.EnableStatus == query.EnableStatus.Value);
            }
            //
            if (query.PaymentType.HasValue) {
                q = q.Where(c => c.PaymentType == query.PaymentType.Value);
            }
            //更新时间排序
            if (query.LastUpdateDateSortType.HasValue) {
                if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.正常.GetHashCode())
                {
                    q = q.OrderByDescending(c => c.BalanceLastUpdate);
                }
                else if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.不正常.GetHashCode())
                {
                    q = q.OrderBy(c => c.BalanceLastUpdate);
                }
            }
            var list = q.Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();
            var total = q.Count();

            #region 数据转换
            var paymentCardResponses = new List<PaymentCardResponse>();

            list.ForEach(item => {
                var ipAddress = item.IpAddress;
                if (!query.UserPermission.Contains("PaymentCard0004"))
                {
                    ipAddress = "*.*.*.*";
                }
                var paymentCardResponse = new PaymentCardResponse() {
                    BcId = item.Bcid,
                    CompanyId = item.CompanyId,
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName = item.CardName,
                    CardType = item.CardType,
                    UsingStatus = item.UsingStatus,
                    EnableStatus = item.EnableStatus,
                    Balance = item.Balance.HasValue ? item.Balance.Value.ToString("#0.00") : null,
                    PaymentStart = item.PaymentStart,
                    PaymentEnd = item.PaymentEnd,
                    PaymentInterval = $"{item.PaymentStart.ToString("#0.00")} - {item.PaymentEnd.ToString("#0.00")}",
                    PayFeeRatio = item.PayFeeRatio*100,
                    BalanceLastUpdate = item.BalanceLastUpdate.HasValue ? item.BalanceLastUpdate.Value.ToString("yyyy-MM-dd HH:mm:ss"):null,
                    //TimeDifference
                    DepositType = WebUtil.ReplaceLevel(item.DepositType),
                    CrossBankPay = item.CrossBankPay,
                    DepositFeeRatio = item.DepositFeeRatio,
                    IpAddress = ipAddress,
                    Remark = item.Remark,
                    UsingStatusRemark = item.UsingStatusRemark,
                    VmClientId = item.VmClientId,
                    Quantity =item.Quantity,
                    WithdrawalAmount = item.WithdrawalAmount.ToString("#0.00"),
                    AdjustBalanceRid = item.AdjustBalanceRid.HasValue? item.AdjustBalanceRid.Value.ToString():null,
                    LimitPayAmount = item.LimitPayAmount,
                    LimitPayCount = item.LimitPayCount,
                    Limit = false,
                    PlatformType = query.PlatformType
                };

                //银行卡号权限
                if (query.UserPermission.Contains("PaymentCard0006"))
                {
                    paymentCardResponse.CardNumber = item.CardNumber;
                }

                if (item.LimitPayCount.HasValue) {
                    if (item.Quantity > item.LimitPayCount.Value && item.LimitPayCount.Value>0) {
                        paymentCardResponse.Limit = true;
                    }
                }

                if (item.LimitPayAmount.HasValue)
                {
                    if (item.WithdrawalAmount > item.LimitPayAmount.Value && item.LimitPayAmount.Value>0)
                    {
                        paymentCardResponse.Limit = true;
                    }
                }

                if (item.BalanceLastUpdate.HasValue)
                {
                    var balanceLastUpdate = item.BalanceLastUpdate.Value;
                    var ts = DateTime.Now.Subtract(balanceLastUpdate).TotalMinutes;
                    if (ts > 0) {
                        paymentCardResponse.TimeDifference = ts;
                    }
                }
                if (query.UserPermission.Contains("PaymentRecord0013"))
                {
                    paymentCardResponse.Remark = "***";
                }

                #region 配置
                if (sysConfigs != null && sysConfigs.Any())
                {
                    var sysConfig = sysConfigs.FirstOrDefault(p => p.ConfigValue == item.PaymentType);
                    if (sysConfig != null)
                    {
                        paymentCardResponse.PaymentTypeName = sysConfig.ConfigContent;
                    }
                }
                #endregion
                paymentCardResponses.Add(paymentCardResponse);
            });
            #endregion

            return new DataGrid<PaymentCardResponse>()
            {
                Total = total,
                Rows = paymentCardResponses,
                Success = true
            };
        }


        /// <summary>
        /// 付款卡查询
        /// </summary>
        /// <param name="query">查询参数</param>
        public PagedList<PaymentCardResponse> GetRenewMatchCardPager(RenewMatchCardQuery query)
        {

            //配置
            var sysConfigs = _context.SysConfig.Where(c => c.ConfigCode == "PaymentInterface").ToList();

            var q = from bc in _context.BankCard
                        let quantity = (from pr in _context.PaymentRecord where pr.PaymentCardId == bc.Bcid && pr.PaymentDate.Value.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd") && pr.PaymentStatus == (sbyte)PaymentRecordPaymentStatus.付款成功  select pr).Count()
                        let withdrawalAmountSum = (from pr in _context.PaymentRecord where pr.PaymentCardId == bc.Bcid && pr.PaymentDate.Value.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd") && pr.PaymentStatus == (sbyte)PaymentRecordPaymentStatus.付款成功 select pr).Sum(p=>p.WithdrawalAmount)
                    join
                    c in _context.Company on bc.CompanyId equals c.CompanyId
                    where bc.CardType == (sbyte)CardType.付款卡.GetHashCode() && bc.CompanyId > 0
                    select new
                    {
                        bc.Bcid,
                        c.CompanyId,
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        bc.CardType,
                        bc.UsingStatus,
                        bc.EnableStatus,
                        bc.Balance,
                        bc.PaymentStart,
                        bc.PaymentEnd,
                        //PaymentInterval
                        bc.PayFeeRatio,
                        bc.BalanceLastUpdate,
                        //TimeDifference
                        bc.PaymentType,
                        bc.DepositType,
                        bc.CrossBankPay,
                        bc.DepositFeeRatio,
                        bc.IpAddress,
                        bc.Remark,
                        bc.VmClientId,
                        //Quantity = quantity,
                        //WithdrawalAmount = withdrawalAmountSum.ToString("#0.00"),
                        bc.AdjustBalanceRid
                    };
            //公司编号
            if (query.CompanyId.HasValue)
            {
                q = q.Where(c => c.CompanyId == query.CompanyId.Value);
            }
            //银行卡启用状态
            if (query.CompanyIds != null && query.CompanyIds.Any())
            {
                q = q.Where(c => query.CompanyIds.Contains(c.CompanyId));
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //银行卡
            if (!string.IsNullOrEmpty(query.BankName))
            {
                q = q.Where(c => c.BankName.Contains(query.BankName));
            }
            //银行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            //銀行卡号
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //银行卡使用状态
            if (query.UsingStatus.HasValue)
            {
                q = q.Where(c => c.UsingStatus == query.UsingStatus.Value);
            }
            //银行卡启用状态
            if (query.EnableStatus.HasValue)
            {
                q = q.Where(c => c.EnableStatus == query.EnableStatus.Value);
            }
            //更新时间排序
            if (query.LastUpdateDateSortType.HasValue)
            {
                if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.正常.GetHashCode())
                {
                    q = q.OrderByDescending(c => c.BalanceLastUpdate);
                }
                else if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.不正常.GetHashCode())
                {
                    q = q.OrderBy(c => c.BalanceLastUpdate);
                }
            }
            var list = q.Skip((query.CurrentPageIndex.Value - 1) * query.PageSize.Value).Take(query.PageSize.Value).ToList();
            var count = q.Count();

            #region 数据转换
            var paymentCardResponses = new List<PaymentCardResponse>();

            list.ForEach(item => {
                var ipAddress = item.IpAddress;
                if (!query.UserPermission.Contains("PaymentCard0004"))
                {
                    ipAddress = "*.*.*.*";
                }
                var paymentCardResponse = new PaymentCardResponse()
                {
                    BcId = item.Bcid,
                    CompanyId = item.CompanyId,
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName = item.CardName,
                    CardType = item.CardType,
                    UsingStatus = item.UsingStatus,
                    EnableStatus = item.EnableStatus,
                    Balance = item.Balance.HasValue ? item.Balance.Value.ToString("#0.00") : null,
                    PaymentStart = item.PaymentStart,
                    PaymentEnd = item.PaymentEnd,
                    PaymentInterval = $"{item.PaymentStart.ToString("#0.00")} - {item.PaymentEnd.ToString("#0.00")}",
                    PayFeeRatio = item.PayFeeRatio * 100,
                    BalanceLastUpdate = item.BalanceLastUpdate.HasValue ? item.BalanceLastUpdate.Value.ToString("yyyy-MM-dd HH:mm:ss") : null,
                    //TimeDifference
                    DepositType = item.DepositType,
                    CrossBankPay = item.CrossBankPay,
                    DepositFeeRatio = item.DepositFeeRatio,
                    IpAddress = ipAddress,
                    Remark = item.Remark,
                    VmClientId = item.VmClientId,
                    Quantity = 0,//item.Quantity
                    WithdrawalAmount = "0",//item.WithdrawalAmount
                    AdjustBalanceRid = item.AdjustBalanceRid.HasValue ? item.AdjustBalanceRid.Value.ToString() : null
                };
                if (item.BalanceLastUpdate.HasValue)
                {
                    var balanceLastUpdate = item.BalanceLastUpdate.Value;
                    var ts = DateTime.Now.Subtract(balanceLastUpdate).TotalMinutes;
                    if (ts > 0)
                    {
                        paymentCardResponse.TimeDifference = ts;
                    }
                }
                #region 配置
                if (sysConfigs != null && sysConfigs.Any())
                {
                    var sysConfig = sysConfigs.FirstOrDefault(p => p.ConfigValue == item.PaymentType);
                    if (sysConfig != null)
                    {
                        paymentCardResponse.PaymentTypeName = sysConfig.ConfigContent;
                    }
                }
                #endregion
                paymentCardResponses.Add(paymentCardResponse);
            });
            #endregion

            return new PagedList<PaymentCardResponse>()
            {
                TData = paymentCardResponses,
                CurrentPageIndex = query.CurrentPageIndex.Value,
                TotalCount = count
            };
        }

        /// <summary>
        /// 收付款记录查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<PaymentRecordResponse> GetPaymentRecordPager(PaymentRecordQuery query)
        {
            var startTime = query.StartTime;
            var endTime = query.EndTime;
            //公司列表
            var companys = _context.Company.Where(c => query.CompanyIds.Contains(c.CompanyId)).ToList();
            //银行卡列表(添加公司过滤)
            var bankCards = _context.BankCard.Where(c=>query.CompanyIds.Contains(c.CompanyId) || c.CompanyId==1).ToList();
            
            //配置
            var sysConfigs = _context.SysConfig.Where(c => c.ConfigCode == "PaymentInterface").ToList();

            var spName = query.DepositOrPaymentType.Equals(DepositOrPaymentType.存款) ? "sp_SelectDepositRecord" : "sp_SelectPaymentRecord";

            var sql = string.Format("call {0}('{1}', '{2}', '{3}');", spName, startTime, endTime, string.Join(", ", query.CompanyIds));


            var q = _context.PaymentRecord
                    .FromSql(sql)
                    .OrderByDescending(x => x.CreateDbdate)
                    .ToList();
            //订单号
            if (!string.IsNullOrEmpty(query.OrderNo)) {
                q = q.Where(c => c.OrderNo.ToString().Contains(query.OrderNo)).ToList();
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                var companySearch = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).ToList();
                q = q.Where(c => companySearch.Select(p => p.CompanyId).Contains(c.CompanyId)).ToList();
            }
            //客户姓名
            if (!string.IsNullOrEmpty(query.WithdrawalAccountName))
            {
                q = q.Where(c => c.WithdrawalAccountName.Contains(query.WithdrawalAccountName)).ToList();
            }
            //客户卡号
            if (!string.IsNullOrEmpty(query.WithdrawalCardNumber))
            {
                q = q.Where(c => c.WithdrawalCardNumber.Contains(query.WithdrawalCardNumber)).ToList();
            }
            //客户订单号
            if (!string.IsNullOrEmpty(query.WithdrawalOrderNo))
            {
                q = q.Where(c => c.WithdrawalOrderNo.Contains(query.WithdrawalOrderNo)).ToList();
            }
            //付款状态
            if (query.PaymentStatus.HasValue)
            {
                q = q.Where(c => c.PaymentStatus == query.PaymentStatus.Value).ToList();
            }
            //通知状态
            if (query.NoticeStatus.HasValue)
            {
                q = q.Where(c => c.NoticeStatus == query.NoticeStatus.Value).ToList();
            }
            //通知状态
            if (query.PaymentType.HasValue)
            {
                q = q.Where(c => c.PaymentType == query.PaymentType.Value).ToList();
            }

            //付款名称
            if (!string.IsNullOrEmpty(query.CardName)) {
                var bankCredIds = _context.BankCard.Where(p => p.CardName.Contains(query.CardName)).Select(p=>p.Bcid).ToList();
                if (bankCredIds != null && bankCredIds.Any()){
                    q = q.Where(c => bankCredIds.Contains(c.PaymentCardId.HasValue ? c.PaymentCardId.Value : 0)).ToList();
                }
                else {
                    q = q.Where(c => bankCredIds.Contains(0)).ToList();
                }
            }
            //付款银行名称
            if (!string.IsNullOrEmpty(query.BankName)){
                var bankCardIds = bankCards.Where(p => p.BankName.Contains(query.BankName)).Select(p => p.Bcid).ToList();
                if (bankCardIds != null && bankCardIds.Any()){
                    q = q.Where(c => bankCardIds.Contains(c.PaymentCardId.HasValue ? c.PaymentCardId.Value : 0)).ToList();
                } else{
                    q = q.Where(c => bankCardIds.Contains(0)).ToList();
                }
            }
            var list = q.Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).OrderByDescending(p=>p.CreateDate).ToList();

            #region 模型转换
            var withdrawalAmount = new decimal(0);

            var paymentRecordResponses = new List<PaymentRecordResponse>();

            list.ForEach(item => {
                var paymentRecordResponse = new PaymentRecordResponse();

                #region 公司
                var companyName = "";
                var companyNameEn = "";
                if (companys != null && companys.Any()) {
                    var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                    if (company != null) {
                        companyName = company.CompanyName;
                        companyNameEn = company.CompanyNameEn;
                    }
                }
                #endregion

                #region 银行卡
                var bankName = "";
                var cardName = "";
                var cardNumber = "";
                var originalBankCardEnableStatus = 0;
                if (bankCards != null && bankCards.Any()){
                    var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.PaymentCardId);
                    if (bankCard != null){
                        bankName = bankCard.BankName;
                        cardName = bankCard.CardName;
                        cardNumber = bankCard.CardNumber;
                        originalBankCardEnableStatus = bankCard.EnableStatus;
                    }
                }
                #endregion

                #region 配置
                var paymentTypeName = "";
                if (item.PaymentType == 0)
                {
                    paymentTypeName = "秒付宝";
                }
                else
                {
                    if (sysConfigs != null && sysConfigs.Any())
                    {
                        var sysConfig = sysConfigs.FirstOrDefault(p => p.ConfigValue == item.PaymentType);
                        if (sysConfig != null)
                        {
                            paymentTypeName = sysConfig.ConfigContent;
                        }
                    }
                }
                #endregion

                withdrawalAmount += item.WithdrawalAmount;

                paymentRecordResponse.OrderNo = item.OrderNo.ToString();
                paymentRecordResponse.CompanyId = item.CompanyId;
                paymentRecordResponse.CompanyName = companyName;
                paymentRecordResponse.CompanyNameEn = companyNameEn;
                paymentRecordResponse.WithdrawalOrderNo = item.WithdrawalOrderNo;
                paymentRecordResponse.BankId = item.PaymentCardId.HasValue ? item.PaymentCardId.Value.ToString() : "-1";
                paymentRecordResponse.BankName = bankName;
                paymentRecordResponse.CardName = cardName;
                paymentRecordResponse.CardNumber = WebUtil.SetCardNumber(cardNumber);
                paymentRecordResponse.WithdrawalAmount = item.WithdrawalAmount.ToString("#0.00");
                paymentRecordResponse.WithdrawalFee = item.WithdrawalFee.ToString("#0.00");
                paymentRecordResponse.PaymentCardId = item.PaymentCardId;
                paymentRecordResponse.PaymentDate = item.PaymentDate;
                paymentRecordResponse.PaymentStatus = item.PaymentStatus;
                paymentRecordResponse.PaymentType = item.PaymentType;
                paymentRecordResponse.PaymentTypeName = paymentTypeName;
                paymentRecordResponse.WithdrawalAccountName = item.WithdrawalAccountName;
                paymentRecordResponse.WithdrawalBankName = item.WithdrawalBankName;
                paymentRecordResponse.WithdrawalCardNumber = item.WithdrawalCardNumber;
                paymentRecordResponse.NoticeStatus = item.NoticeStatus;
                paymentRecordResponse.ConfirmStatus = item.ConfirmStatus;
                paymentRecordResponse.PaymentRemark = string.IsNullOrEmpty(item.PaymentRemark)?"": item.PaymentRemark;

                if (item.NoticeLastDate.HasValue)
                {
                    paymentRecordResponse.NoticeLastDate = item.NoticeLastDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
                }
                else {
                    paymentRecordResponse.NoticeLastDate = null;
                }


                paymentRecordResponse.NoticeTimes = item.NoticeTimes;
                paymentRecordResponse.DepositType = WebUtil.ReplaceLevel(item.DepositType);
                paymentRecordResponse.CreateDbdate = item.CreateDbdate;
                //(分钟)(当前时间-付款记录创建时间)>20   显示查询按钮
                var ts = DateTime.Now.Subtract(item.CreateDbdate).TotalMinutes;
                if (ts > 0)
                {
                    paymentRecordResponse.TimeDifference = ts;
                }
                //(分钟)(当前时间-派发时间)>30   显示查询重新配卡
                if (item.AllocatedTime.HasValue) {
                    var tss = DateTime.Now.Subtract(item.AllocatedTime.Value).TotalMinutes;
                    if (tss > 0)
                    {
                        paymentRecordResponse.RenewMatchCardTimeDifference = tss;
                    }
                }

                if (item.PaymentStatus == (sbyte)PaymentRecordPaymentStatus.付款中 && item.ReadDate.HasValue)
                {
                    var tsss = DateTime.Now.Subtract(item.ReadDate.Value).TotalMinutes;
                    if (tsss > 0)
                    {
                        paymentRecordResponse.PaymentDateTimeDifference = tsss;
                    }

                    var paymentAndReadTimeDifference = item.ReadDate.Value.Subtract(item.CreateDbdate).TotalMinutes;
                    if (paymentAndReadTimeDifference > 0) {
                        paymentRecordResponse.PaymentAndReadTimeDifference = paymentAndReadTimeDifference;
                    }
                }
                else if(item.PaymentDate.HasValue) {
                    var tsss = DateTime.Now.Subtract(item.PaymentDate.Value).TotalMinutes;
                    if (tsss > 0)
                    {
                        paymentRecordResponse.PaymentDateTimeDifference = tsss;
                    }

                    var paymentAndReadTimeDifference = item.PaymentDate.Value.Subtract(item.CreateDbdate).TotalMinutes;
                    if (paymentAndReadTimeDifference > 0)
                    {
                        paymentRecordResponse.PaymentAndReadTimeDifference = paymentAndReadTimeDifference;
                    }
                }

                if (query.UserPermission != null && query.UserPermission.Contains("PaymentRecord0014")) {
                    paymentRecordResponse.ChangeChannelsPermission = "PaymentRecord0014";
                }
                if (query.UserPermission != null && query.UserPermission.Contains("PaymentRecord0015"))
                {
                    paymentRecordResponse.ChangePaymentRecordCompanyPermission = "PaymentRecord0015";
                }
                paymentRecordResponse.OriginalBankCardEnableStatus = originalBankCardEnableStatus;
                paymentRecordResponse.ReadDate = item.ReadDate.HasValue ? item.ReadDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                paymentRecordResponse.PaymentFailInfo = item.PaymentFailInfo;
                paymentRecordResponses.Add(paymentRecordResponse);
            });

            var footers = new List<PaymentRecordResponse>();

            footers.Add(new PaymentRecordResponse()
            {
                CardNumber = "订单总额",
                WithdrawalAmount = withdrawalAmount.ToString("#0.00"),
                WithdrawalFee = ""
            });
            #endregion

            return new DataGrid<PaymentRecordResponse>() {
                Total = q.Count(),
                Rows = paymentRecordResponses,
                Footer = footers,
                Success = true
             };
        }

        /// <summary>
        /// 收付款记录查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<PaymentRecordResponse> GetPaymentRecordExport(PaymentRecordQuery query)
        {
            var startTime = query.StartTime;
            var endTime = query.EndTime;
            //公司列表
            var companys = _context.Company.ToList();
            //银行卡列表
            var bankCards = _context.BankCard.ToList();
            //配置
            var sysConfigs = _context.SysConfig.Where(c => c.ConfigCode == "PaymentInterface").ToList();

            var sql = string.Format("call sp_SelectPaymentRecordByPaymentDateWeb('{0}', '{1}', '{2}');", startTime, endTime, string.Join(", ", query.CompanyIds));


            var q = _context.PaymentRecord
                    .FromSql(sql).OrderByDescending(x => x.CreateDbdate)
                    .ToList();
            //订单号
            if (!string.IsNullOrEmpty(query.OrderNo))
            {
                q = q.Where(c => c.OrderNo.ToString().Contains(query.OrderNo)).ToList();
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                var companySearch = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).ToList();
                q = q.Where(c => companySearch.Select(p => p.CompanyId).Contains(c.CompanyId)).ToList();
            }
            //客户姓名
            if (!string.IsNullOrEmpty(query.WithdrawalAccountName))
            {
                q = q.Where(c => c.WithdrawalAccountName.Contains(query.WithdrawalAccountName)).ToList();
            }
            //客户卡号
            if (!string.IsNullOrEmpty(query.WithdrawalCardNumber))
            {
                q = q.Where(c => c.WithdrawalCardNumber.Contains(query.WithdrawalCardNumber)).ToList();
            }
            //客户订单号
            if (!string.IsNullOrEmpty(query.WithdrawalOrderNo))
            {
                q = q.Where(c => c.WithdrawalOrderNo.Contains(query.WithdrawalOrderNo)).ToList();
            }
            //付款状态
            if (query.PaymentStatus.HasValue)
            {
                q = q.Where(c => c.PaymentStatus == query.PaymentStatus.Value).ToList();
            }
            //通知状态
            if (query.NoticeStatus.HasValue)
            {
                q = q.Where(c => c.NoticeStatus == query.NoticeStatus.Value).ToList();
            }
            //通知状态
            if (query.PaymentType.HasValue)
            {
                q = q.Where(c => c.PaymentType == query.PaymentType.Value).ToList();
            }

            //付款名称
            if (!string.IsNullOrEmpty(query.CardName))
            {
                var bankCredIds = _context.BankCard.Where(p => p.CardName.Contains(query.CardName)).Select(p => p.Bcid).ToList();
                if (bankCredIds != null && bankCredIds.Any())
                {
                    q = q.Where(c => bankCredIds.Contains(c.PaymentCardId.HasValue ? c.PaymentCardId.Value : 0)).ToList();
                }
            }

            var list = q.OrderByDescending(p => p.CreateDate).ToList();

            #region 模型转换
            var withdrawalAmount = new decimal(0);

            var paymentRecordResponses = new List<PaymentRecordResponse>();

            list.ForEach(item => {
                var paymentRecordResponse = new PaymentRecordResponse();

                #region 公司
                var companyName = "";
                var companyNameEn = "";
                if (companys != null && companys.Any())
                {
                    var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                    if (company != null)
                    {
                        companyName = company.CompanyName;
                        companyNameEn = company.CompanyNameEn;
                    }
                }
                #endregion

                #region 银行卡
                var bankName = "";
                var cardName = "";
                var cardNumber = "";
                var originalBankCardEnableStatus = 0;
                if (bankCards != null && bankCards.Any())
                {
                    var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.PaymentCardId);
                    if (bankCard != null)
                    {
                        bankName = bankCard.BankName;
                        cardName = bankCard.CardName;
                        cardNumber = bankCard.CardNumber;
                        originalBankCardEnableStatus = bankCard.EnableStatus;
                    }
                }
                #endregion

                #region 配置
                var paymentTypeName = "";
                if (item.PaymentType == 0)
                {
                    paymentTypeName = "秒付宝";
                }
                else
                {
                    if (sysConfigs != null && sysConfigs.Any())
                    {
                        var sysConfig = sysConfigs.FirstOrDefault(p => p.ConfigValue == item.PaymentType);
                        if (sysConfig != null)
                        {
                            paymentTypeName = sysConfig.ConfigContent;
                        }
                    }
                }

                #endregion

                withdrawalAmount += item.WithdrawalAmount;

                paymentRecordResponse.OrderNo = item.OrderNo.ToString();
                paymentRecordResponse.CompanyName = companyName;
                paymentRecordResponse.CompanyNameEn = companyNameEn;
                paymentRecordResponse.WithdrawalOrderNo = item.WithdrawalOrderNo;
                paymentRecordResponse.BankId = item.PaymentCardId.HasValue ? item.PaymentCardId.Value.ToString() : "-1";
                paymentRecordResponse.BankName = bankName;
                paymentRecordResponse.CardName = cardName;
                paymentRecordResponse.CardNumber = WebUtil.SetCardNumber(cardNumber);
                paymentRecordResponse.WithdrawalAmount = item.WithdrawalAmount.ToString("#0.00");
                paymentRecordResponse.WithdrawalFee = item.WithdrawalFee.ToString("#0.00");
                paymentRecordResponse.PaymentCardId = item.PaymentCardId;
                paymentRecordResponse.PaymentDate = item.PaymentDate;
                paymentRecordResponse.PaymentStatus = item.PaymentStatus;
                paymentRecordResponse.PaymentType = item.PaymentType;
                paymentRecordResponse.PaymentTypeName = paymentTypeName;
                paymentRecordResponse.WithdrawalAccountName = item.WithdrawalAccountName;
                paymentRecordResponse.WithdrawalBankName = item.WithdrawalBankName;
                paymentRecordResponse.WithdrawalCardNumber = item.WithdrawalCardNumber;
                paymentRecordResponse.NoticeStatus = item.NoticeStatus;
                paymentRecordResponse.ConfirmStatus = item.ConfirmStatus;

                if (item.NoticeLastDate.HasValue)
                {
                    paymentRecordResponse.NoticeLastDate = item.NoticeLastDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
                }
                else
                {
                    paymentRecordResponse.NoticeLastDate = null;
                }


                paymentRecordResponse.NoticeTimes = item.NoticeTimes;
                paymentRecordResponse.DepositType = item.DepositType;
                paymentRecordResponse.CreateDbdate = item.CreateDbdate;
                //(分钟)(当前时间-付款记录创建时间)>20   显示查询按钮
                var ts = DateTime.Now.Subtract(item.CreateDbdate).TotalMinutes;
                if (ts > 0)
                {
                    paymentRecordResponse.TimeDifference = ts;
                }
                //(分钟)(当前时间-派发时间)>30   显示查询重新配卡
                if (item.AllocatedTime.HasValue)
                {
                    var tss = DateTime.Now.Subtract(item.AllocatedTime.Value).TotalMinutes;
                    if (tss > 0)
                    {
                        paymentRecordResponse.RenewMatchCardTimeDifference = tss;
                    }
                }
                paymentRecordResponse.OriginalBankCardEnableStatus = originalBankCardEnableStatus;

                paymentRecordResponse.ReadDate = item.ReadDate.HasValue ? item.ReadDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : "";
                paymentRecordResponse.PaymentFailInfo = item.PaymentFailInfo;
                paymentRecordResponses.Add(paymentRecordResponse);
            });

            var footers = new List<PaymentRecordResponse>();

            footers.Add(new PaymentRecordResponse()
            {
                CardNumber = "订单总额",
                WithdrawalAmount = withdrawalAmount.ToString("#0.00")
            });
            #endregion

            return new DataGrid<PaymentRecordResponse>()
            {
                Total = q.Count(),
                Rows = paymentRecordResponses,
                Footer = footers,
                Success = true
            };
        }

        /// <summary>
        /// 补单
        /// </summary>
        /// <param name="request"></param>
        public JResult ReceiptMakeUp(ReceiptMakeUpRequest request)
        {
            var result = new JResult() {
                Success = false
            };

            var recordRealTableName = $"RecordReal_{DateTime.Now.ToString("MM")}";
            var depositRecordTableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(request.OrderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                recordRealTableName = $"RecordReal_{month}";
                depositRecordTableName = $"DepositRecord_{month}";
            }

            var recordReal = this.GetRecordReal(request.OrderNo);
            if (recordReal == null) {
                throw new Exception("未入账收款记录不存在！");
            }

            //类型验证
            if (recordReal.RecordType != RecordType.存款.GetHashCode())
            {
                throw new Exception("改订单收、付款类型错误！");
            }

            //匹配单号验证
            if (recordReal.MatchOrderNo.HasValue)
            {
                throw new Exception("改收款单已匹配单号！");
            }

            //获取银行卡
            var bankCard = _context.BankCard.FirstOrDefault(p => p.Bcid == recordReal.CardBcid);
            if (bankCard == null)
            {
                throw new Exception("实际存款记录的银行卡不存在！");
            }

            //获取公司
            var company = _context.Company.FirstOrDefault(p => p.CompanyId == recordReal.CompanyId);
            if (bankCard == null)
            {
                throw new Exception("实际存款记录的公司不存在！");
            }

            if (request.Transstype <= 0)
            {
                throw new Exception("请选择补单类型！");
            }
            //获取配置
            var configs = _context.SysConfig.Where(p => p.ConfigCode == "Transtype" && p.CompanyId == 2).ToList();
            if (configs == null)
            {
                throw new Exception("获取配置信息失败！");
            }

            var sysConfig = configs.FirstOrDefault(p => p.ConfigValue == request.Transstype);
            if (sysConfig == null) {
                throw new Exception("被单类型不合法！");
            }

            var connection = _context.Database.GetDbConnection();
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            try
            {
                var insertSatatus = 0;

                var feeRatio = bankCard.DepositFeeRatio <= 0 ? company.DepositRate.Value : bankCard.DepositFeeRatio;
                var feeTotal = feeRatio * recordReal.Amount;

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "call sp_InsertDepositRecordWeb(@_DepositNo,@_FeeRatio,@_FeeTotal,@_BeforeBalance,@_AfterBalance,@_CompanyId,@_DepositRemark,@_CreateDate,@_NoticeStatus,@_NoticeLastTime,@_NoticeTimes,@_DepositDate,@_DepositCardId,@_CardNumber,@_DepositAmount,@_ClientBankName,@_ClientAccountName,@_ClientCardNumber,@_BankSerialNo,@_PostScript,@_Transtype,@_RealRecordOrderNo);";

                    command.Parameters.Add(new MySqlParameter("_DepositNo", recordReal.ClientOrderNo));
                    command.Parameters.Add(new MySqlParameter("_FeeRatio", feeRatio));
                    command.Parameters.Add(new MySqlParameter("_FeeTotal", feeTotal));
                    command.Parameters.Add(new MySqlParameter("_BeforeBalance", recordReal.AfterBalance - recordReal.Amount));
                    command.Parameters.Add(new MySqlParameter("_AfterBalance", recordReal.AfterBalance));
                    command.Parameters.Add(new MySqlParameter("_CompanyId", recordReal.CompanyId));
                    command.Parameters.Add(new MySqlParameter("_DepositRemark", recordReal.Remark));
                    command.Parameters.Add(new MySqlParameter("_CreateDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                    command.Parameters.Add(new MySqlParameter("_NoticeStatus", (sbyte)DepositRecordNoticeStatus.通知失败.GetHashCode()));
                    command.Parameters.Add(new MySqlParameter("_NoticeLastTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                    command.Parameters.Add(new MySqlParameter("_NoticeTimes", 1));
                    command.Parameters.Add(new MySqlParameter("_DepositDate", recordReal.CreateDbdate.ToString("yyyy-MM-dd HH:mm:ss")));
                    command.Parameters.Add(new MySqlParameter("_DepositCardId", recordReal.CardBcid));
                    command.Parameters.Add(new MySqlParameter("_CardNumber", bankCard.CardNumber));
                    command.Parameters.Add(new MySqlParameter("_DepositAmount", recordReal.Amount));
                    command.Parameters.Add(new MySqlParameter("_ClientBankName", recordReal.ClientBankName));
                    command.Parameters.Add(new MySqlParameter("_ClientAccountName", recordReal.ClientAccountName));
                    command.Parameters.Add(new MySqlParameter("_ClientCardNumber", recordReal.ClientCardNumber));
                    command.Parameters.Add(new MySqlParameter("_BankSerialNo", recordReal.BankSerialNo));
                    command.Parameters.Add(new MySqlParameter("_PostScript", ""));
                    command.Parameters.Add(new MySqlParameter("_Transtype", request.Transstype));
                    command.Parameters.Add(new MySqlParameter("_RealRecordOrderNo", recordReal.OrderNo));

                    using (var insertResult = command.ExecuteReader())
                    {
                        if (insertResult.Read())
                        {
                            insertSatatus = insertResult.GetInt32(0);
                        }
                    }
                }

                if (insertSatatus > 0){
                    result.Success = true;
                }
                else {
                    result.ErrorMessage = "更新实际存款记录匹配单号失败";
                }
                
            }
            catch (Exception ex) {
                result.ErrorMessage = ex.Message;
            }
            

            #region 旧代码
            //using (var tran = _context.Database.BeginTransaction()) {
            //    try
            //    {
            //        var feeRatio = bankCard.DepositFeeRatio <= 0 ? company.DepositRate.Value : bankCard.DepositFeeRatio;
            //        var feeTotal = feeRatio * recordReal.Amount;

            //        //收款明细
            //        var depositRecord = new DepositRecord()
            //        {
            //            DepositNo = recordReal.ClientOrderNo,
            //            FeeRatio = feeRatio,
            //            FeeTotal = feeTotal,
            //            BeforeBalance = recordReal.AfterBalance - recordReal.Amount,
            //            AfterBalance = recordReal.AfterBalance,
            //            CompanyId = recordReal.CompanyId,
            //            DepositRemark = recordReal.Remark,
            //            CreateDate = DateTime.Now,
            //            NoticeStatus = (sbyte)DepositRecordNoticeStatus.通知失败.GetHashCode(),
            //            NoticeTimes = 1,
            //            NoticeLastTime = DateTime.Now,
            //            DepositDate = recordReal.CreateDbdate,
            //            DepositCardId = recordReal.CardBcid,
            //            CardNumber = bankCard.CardNumber,
            //            DepositAmount = recordReal.Amount,
            //            ClientBankName = recordReal.ClientBankName,
            //            ClientAccountName = recordReal.ClientAccountName,
            //            ClientCardNumber = recordReal.ClientCardNumber,
            //            BankSerialNo = recordReal.BankSerialNo,
            //            PostScript = "",
            //            Transtype = request.Transstype,
            //        };

            //        //新增收款明细
            //        _context.DepositRecord.Add(depositRecord);
            //        int depositRecordId = _context.SaveChanges();

            //        if (depositRecord.OrderNo <= 0)
            //        {
            //            throw new Exception("新增收款明细成功！");
            //        }

            //        var updateRecordReal = _context.RecordReal.FirstOrDefault(p => p.OrderNo == request.OrderNo);
            //        if (updateRecordReal == null)
            //        {
            //            throw new Exception("未入账收款记录不存在！");
            //        }

            //        updateRecordReal.MatchOrderNo = depositRecord.OrderNo;
            //        int updateStatus = _context.SaveChanges();

            //        if (updateStatus <= 0)
            //        {
            //            throw new Exception("更新实际存款记录匹配单号失败！");
            //        }

            //        result.Success = true;
            //        tran.Commit();
            //    }
            //    catch (Exception ex) {
            //        tran.Rollback();

            //        result.ErrorMessage = ex.Message;
            //    }
            //}
            #endregion
            return result;
        }

        /// <summary>
        /// 重新配卡
        /// </summary>
        /// <returns></returns>
        public JResult RenewMatchCard(RenewMatchCardReqeust reqeust)
        {
            var result = new JResult()
            {
                Success = false
            };

            try
            {
                var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

                var month = GetOrderMonth(reqeust.PaymentRecordId);
                if (month != DateTime.Now.ToString("MM"))
                {
                    tableName = $"PaymentRecord_{month}";
                }

                var originalPaymentCardNo = reqeust.OriginalPaymentCardId.Equals(-1) ? "无" : reqeust.OriginalPaymentCardId.ToString();
                //获取原付款卡
                if (reqeust.OriginalPaymentCardId != -1) {

                    var oldBankCard = _context.BankCard.FirstOrDefault(p => p.Bcid == reqeust.OriginalPaymentCardId && p.CardType == (sbyte)CardType.付款卡.GetHashCode());
                    if (oldBankCard == null)
                    {
                        throw new Exception("原付款卡不存在！");
                    }

                    oldBankCard = _context.BankCard.FirstOrDefault(p => p.Bcid == reqeust.OriginalPaymentCardId && p.CardType == (sbyte)CardType.付款卡.GetHashCode() && p.EnableStatus == (sbyte)EnableStatus.禁用.GetHashCode());
                    if (oldBankCard == null)
                    {
                        throw new Exception("原付款卡必须为禁用状态！");
                    }

                    originalPaymentCardNo = $"{oldBankCard.BankName} {oldBankCard.CardNumber} {oldBankCard.CardName}";
                }
                //获取新付款卡
                var bankCard = _context.BankCard.FirstOrDefault(p => p.Bcid == reqeust.PaymentCardId && p.CardType == (sbyte)CardType.付款卡.GetHashCode());
                if (bankCard == null) {
                    throw new Exception("付款卡不存在！");
                }
                //获取付款记录
                var paymentRecord = this.Get(reqeust.PaymentRecordId);
                if (paymentRecord == null)
                {
                    throw new Exception("付款记录不存在！");
                }

                //付款类型验证
                if (paymentRecord.PaymentType != (sbyte)PaymentRecordPaymentType.秒付宝支付.GetHashCode())
                {
                    throw new Exception("付款类型为秒付宝才能重新配单！");
                }

                //跨行验证
                if (bankCard.CrossBankPay == (sbyte)CrossBankPay.同行)
                {
                    if (bankCard.BankCode != paymentRecord.WithdrawalCardBankFlag) {
                        throw new Exception("该付款卡只支持同行付款！");
                    }
                }

                //付款区间验证
                if (!(bankCard.PaymentStart <= paymentRecord.WithdrawalAmount && bankCard.PaymentEnd >= paymentRecord.WithdrawalAmount))
                {
                    throw new Exception("新付款卡的区间不在付款记录范围内");
                }

                if (bankCard.CardNumber == paymentRecord.WithdrawalCardNumber)
                {
                    throw new Exception("付款卡号不能等于收款卡号");
                }

                var feeRatio = bankCard.PayFeeRatio;
                var feeTotal = bankCard.PayFeeRatio * paymentRecord.WithdrawalAmount;

                var insertSatatus = 0;

                var connection = _context.Database.GetDbConnection();
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "call sp_UpdateAllocatedCardWeb(@OrderNo,@FeeRatio,@FeeTotal,@PaymentCardId,@CardNumber,@AllocatedTime,@OriginalPaymentCardId,@PaymentType);";

                    command.Parameters.Add(new MySqlParameter("OrderNo", reqeust.PaymentRecordId));
                    command.Parameters.Add(new MySqlParameter("FeeRatio", feeRatio));
                    command.Parameters.Add(new MySqlParameter("FeeTotal", feeTotal));
                    command.Parameters.Add(new MySqlParameter("PaymentCardId", reqeust.PaymentCardId));
                    command.Parameters.Add(new MySqlParameter("CardNumber", bankCard.CardNumber));
                    command.Parameters.Add(new MySqlParameter("AllocatedTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                    if (reqeust.OriginalPaymentCardId == -1){
                        command.Parameters.Add(new MySqlParameter("OriginalPaymentCardId", null));
                    }else {
                        command.Parameters.Add(new MySqlParameter("OriginalPaymentCardId", reqeust.OriginalPaymentCardId));
                    }
                    command.Parameters.Add(new MySqlParameter("PaymentType", (sbyte)PaymentRecordPaymentType.秒付宝支付.GetHashCode()));
                    using (var insertResult = command.ExecuteReader())
                    {
                        if (insertResult.Read())
                        {
                            insertSatatus = insertResult.GetInt32(0);
                        }
                    }

                    if (insertSatatus <= 0)
                    {
                        //日志
                        _logRecord.Ip = reqeust.Ip;
                        _logRecord.LogType = LogRecordLogType.PaymentRecord_Reject.GetHashCode();
                        _logRecord.CreateName = reqeust.UserName;
                        _logRecord.LogLevel = (sbyte)LogLevel.Failure.GetHashCode();
                        _logRecord.CreateDate = DateTime.Now;
                        _logRecord.OrderNo = reqeust.PaymentRecordId;
                        _logRecord.LogRemark = $"手动重新配卡失败,订单号：{reqeust.PaymentRecordId},原付款卡号：{originalPaymentCardNo}, 新付款卡：{bankCard.BankName} {bankCard.CardNumber} {bankCard.CardName}";
                        _context.LogRecord.Add(_logRecord);
                        _context.SaveChanges();

                        throw new Exception("付款记录已发生改变！");
                    }
                    else
                    {
                        //日志
                        _logRecord.Ip = reqeust.Ip;
                        _logRecord.LogType = LogRecordLogType.PaymentRecord_Reject.GetHashCode();
                        _logRecord.CreateName = reqeust.UserName;
                        _logRecord.LogLevel = (sbyte)LogLevel.Success.GetHashCode();
                        _logRecord.CreateDate = DateTime.Now;
                        _logRecord.OrderNo = reqeust.PaymentRecordId;
                        _logRecord.LogRemark = $"手动重新配卡成功,订单号：{reqeust.PaymentRecordId},原付款卡号：{originalPaymentCardNo}, 新付款卡：{bankCard.BankName} {bankCard.CardNumber} {bankCard.CardName}";
                        _context.LogRecord.Add(_logRecord);
                        _context.SaveChanges();

                        result.Success = true;
                        result.SuccessMessage = "操作成功！";
                    }
                }
            }
            catch (Exception ex) {
                result.Success = false;
                result.ErrorMessage = ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 改变渠道
        /// </summary>
        /// <param name="request"></param>
        public JResult ChangeChannels(ChangeChannelsRequest request)
        {
            var result = new JResult()
            {
                Success = false
            };

            try
            {
                var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

                var month = GetOrderMonth(request.OrderNo);

                if (month != DateTime.Now.ToString("MM"))
                {
                    tableName = $"PaymentRecord_{month}";
                }

                /*获取付款记录*/
                var paymentRecord = this.Get(request.OrderNo);
                if (paymentRecord is null) {
                    throw new Exception("获取付款记录失败");
                }

                if (!request.PaymentType.HasValue)
                {
                    throw new Exception("选择付款渠道");
                }

                if (paymentRecord.PaymentType == request.PaymentType)
                {
                    throw new Exception("渠道未发生改变");
                }
                //更新Sql
                var updateStatusPaymentRecordSql = $"update {tableName} set PaymentType={request.PaymentType} WHERE ConfirmStatus in(2,3) and ReadDate is NULL and PaymentType={paymentRecord.PaymentType} and PaymentStatus=1 and PaymentDate is NULL and NoticeStatus=1 and NoticeTimes=0 and PaymentCardID is NULL and OrderNo={paymentRecord.OrderNo}";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateStatusPaymentRecordSql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("改变渠道失败、条件不满足！");
                }

                result.Success = true;
            }
            catch (Exception ex) {
                result.ErrorMessage = ex.Message;
            }

            return result;
        }

        /// <summary>
        /// 改变公司
        /// </summary>
        /// <param name="request"></param>
        public JResult ChangeCompany(ChangeCompanyRequest request)
        {
            var result = new JResult()
            {
                Success = false
            };

            var tableName = $"PaymentRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(request.OrderNo);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"PaymentRecord_{month}";
            }

            /*获取付款记录*/
            var paymentRecord = this.Get(request.OrderNo);
            if (paymentRecord is null)
            {
                throw new Exception("获取付款记录失败");
            }

            if (request.CompanyId == paymentRecord.CompanyId)
            {
                throw new Exception("公司未发生变化");
            }

            var updateStatusPaymentRecordSql = $"update {tableName} set CompanyId={request.CompanyId} WHERE ConfirmStatus in(2,3) and ReadDate IS NOT NULL and PaymentDate IS NOT NULL and PaymentStatus=3 and NoticeTimes=5 and PaymentCardID IS NOT NULL and OrderNo={paymentRecord.OrderNo}";

            var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(updateStatusPaymentRecordSql);
            if (updateStatusPaymentRecord <= 0)
            {
                throw new Exception("改变渠道失败、条件不满足！");
            }

            result.Success = true;
            return result;
        }
    }
}
